***RETURNS***

preserve

***S&P Index

import excel "${original}thomreut_returns.xlsx", sheet("stck") firstrow clear
ren (Date SP500CompositeIndex) (date stck)
g year = substr(date,-4,4)
g month = substr(date,-10,2)
replace month = substr(date,-9,1) if month == ""
destring,replace
g ret_stck = (stck-stck[_n-1])/stck[_n-1]
drop stck date
save ret_stck,replace

***10-year Government Bonds Yield

import excel "${original}thomreut_returns.xlsx", sheet("bond") firstrow clear
ren (Date USTreas10yrGovtBondYield) (date ret_bond)
g year = substr(date,-4,4)
g month = substr(date,-10,2)
replace month = substr(date,-9,1) if month == ""
destring,replace
drop date
replace ret_bond = ret_bond/100
save ret_bond,replace

***3-month CDs

import excel "${original}thomreut_returns.xlsx", sheet("cd") firstrow clear
ren (Date RATE3MONTHCDSUnitedStates) (date ret_cd)
g year = substr(date,-4,4)
g month = substr(date,-10,2)
replace month = substr(date,-9,1) if month == ""
destring,replace
drop date
replace ret_cd = ret_cd/100
save ret_cd,replace

***24-month Interest Rate Commercial Banks : United States

import excel "${original}thomreut_returns.xlsx", sheet("debt") firstrow clear
ren (Date CONSUMERCREDITCOMMBKSINTERE) (date ret_debt)
drop if ret_debt == "n/a"
g year = substr(date,-4,4)
g month = substr(date,-10,2)
replace month = substr(date,-9,1) if month == ""
destring,replace
drop date
replace ret_debt = ret_debt/100
save ret_debt,replace

***Federal Funds Target Rate

import excel "${original}thomreut_returns.xlsx", sheet("chck") firstrow clear
ren (Date FederalFundsTargetRateEP) (date ret_chck2)
g year = substr(date,-4,4)
g month = substr(date,-10,2)
replace month = substr(date,-9,1) if month == ""
destring,replace
drop date
replace ret_chck2 = ret_chck2/100
save ret_chck,replace

***Checking and Savings Accounts (Statista)

import excel "${original}statista_returns.xlsx", sheet("Sheet1") clear
ren (A B) (year ret_chck)
save ret_save,replace

*Merge
use ret_stck,clear
merge 1:1 year month using ret_bond,nogen
merge 1:1 year month using ret_chck,nogen
merge 1:1 year month using ret_cd,nogen
merge 1:1 year month using ret_debt,nogen
merge m:1 year using ret_save,nogen
gsort - year - month
replace ret_cd = ret_cd[_n-1] if ret_cd == . // data are quarterly
replace ret_debt = ret_debt[_n-1] if ret_debt == . // data are quarterly
g d_chck = ret_chck2 - ret_chck2[_n-1]
replace ret_chck = ret_chck[_n-1] + d_chck if ret_chck == .
sort year month
replace ret_chck = ret_chck[_n-1] + d_chck if ret_chck == .
drop d_chck
save thomreut_returns,replace

use thomreut_returns,clear
drop if year < 1995 // HRS sample is from 1996 onwards, with some iviews taking place at the end of 1995
unab ret: ret*
foreach r of local ret {
	forval n = 1/48 {
		g `r'_tp`n' = `r'[_n+`n']
		g `r'_tm`n' = `r'[_n-`n']
	}
}
save ret_assets,replace

*Erase temporary datasets
erase ret_stck.dta 
erase ret_bond.dta 
erase ret_chck.dta 
erase ret_cd.dta 
erase ret_debt.dta 
erase ret_save.dta 
erase thomreut_returns.dta

restore